package com.etnoe.gis.common.utils;

import com.etnoe.gis.erro.CustomParameterizedException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @author: 蒋学伟
 * @create: 2020-06-16 11:07:19
 * @Description ExcelUtil
 */
@Component
public class ExcelUtil {


    public static String xls = ".xls";
    public static String xlsx = ".xlsx";

    /**
     * 读取excel,只读取第一个sheel
     *
     * @param file
     * @return
     */
    public static List<ExcelShell> read(MultipartFile file) {
        List<ExcelShell> excelShells = new ArrayList<>();
        Workbook workbook = getWorkbook(file);
        Iterator<Sheet> sheetIterator = workbook.sheetIterator();
        if (sheetIterator.hasNext()) {
            Sheet sheet = sheetIterator.next();
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum >= 0) {
                ExcelShell excelShell = new ExcelShell();

                //头部信息
                Row headRow = sheet.getRow(0);
                if (headRow != null) {
                    short headLastCellNum = headRow.getLastCellNum();
                    List<String> headData = new ArrayList<>();
                    for (int i = 0; i < headLastCellNum; i++) {
                        Cell cell = headRow.getCell(i);
                        cell.setCellType(CellType.STRING);
                        headData.add(cell.getStringCellValue());
                    }
                    if (!CollectionUtils.isEmpty(headData)) {
                        excelShell.setHeads(headData);
                    }
                }


                //内容
                List<List<Object>> contents = new ArrayList<>();
                for (int i = 1; i <= lastRowNum; i++) {
                    Row row = sheet.getRow(i);
                    List<Object> rowList = new ArrayList<>();
                    short lastCellNum = row.getLastCellNum();
                    for (int j = 0; j < lastCellNum; j++) {
                        Cell cell = row.getCell(j);
                        if (cell != null) {
                            switch (cell.getCellType()) {
                                case STRING:
                                    rowList.add(cell.getStringCellValue());
                                    break;
                                case BOOLEAN:
                                    rowList.add(cell.getBooleanCellValue());
                                    break;
                                case NUMERIC:
                                    rowList.add(cell.getNumericCellValue());
                                    break;
                                default:
                                    cell.setCellType(CellType.STRING);
                                    rowList.add(cell.getStringCellValue());
                                    break;
                            }
                        } else {
                            rowList.add(null);
                        }
                    }
                    if (!CollectionUtils.isEmpty(rowList)) {
                        contents.add(rowList);
                    }
                }
                if (!CollectionUtils.isEmpty(contents)) {
                    excelShell.setContents(contents);
                }
                excelShells.add(excelShell);
            }
        }

        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return excelShells;
    }


    /**
     * 读取Workbook
     *
     * @param file
     * @return
     */
    public static Workbook getWorkbook(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        InputStream is = null;
        try {
            is = file.getInputStream();
            if (fileName.endsWith(xls)) {
                //2003
                return new HSSFWorkbook(is);
            } else if (fileName.endsWith(xlsx)) {
                //2007
                return new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        throw new CustomParameterizedException("不是有效excel文件");

    }

    /**
     * 单个excel文件导出
     *
     * @param fileName
     * @param header
     * @param contents
     * @param response
     */
    public static void export(String fileName, List<String> header, List<List<String>> contents, HttpServletResponse response) {
        //声明一个工作簿
        XSSFWorkbook workbook = null;
        try {
            workbook = new XSSFWorkbook();
            //创建一个sheet
            XSSFSheet sheet = workbook.createSheet(fileName);

            //创建第一行表头
            XSSFRow headRow = sheet.createRow(0);

            //遍历添加表头(下面模拟遍历学生，也是同样的操作过程)
            for (int i = 0; i < header.size(); i++) {
                XSSFCell cell = headRow.createCell(i);
                cell.setCellValue(header.get(i));
            }
            for (int i = 0; i < contents.size(); i++) {
                XSSFRow contentRow = sheet.createRow(i + 1);
                List<String> rows = contents.get(i);
                for (int j = 0; j < rows.size(); j++) {
                    XSSFCell cell = contentRow.createCell(j);
                    cell.setCellValue(rows.get(j));
                }
            }
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            response.flushBuffer();
            //workbook将Excel写入到response的输出流中，供页面下载
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
            throw new CustomParameterizedException(e.getMessage());
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }

            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


}
